﻿using Kingdee.BOS.App.Data;
using Kingdee.BOS;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DM.Report
{
    [HotUpdate]
    [Description("委外单品价目表")]
    public class OutsourcingPriceList: SysReportBaseService
    {
        /// 初始化事件：在此事件中，设置报表基本属性
        public override void Initialize()
        {
            base.Initialize();
            // 简单账表类型：普通、树形、分页
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.BillKeyFieldName = "最顶级BOM内码";
            // 报表名称
            this.ReportProperty.ReportName = new LocaleValue("委外单品价目表", base.Context.UserLocale.LCID);
            this.IsCreateTempTableByPlugin = true;
            this.ReportProperty.IsUIDesignerColumns = true;
            this.ReportProperty.IsGroupSummary = true;
            this.ReportProperty.SimpleAllCols = false;
            // 单据主键：两行FID相同，则为同一单的两条分录，单据编号可以不重复显示
            this.ReportProperty.PrimaryKeyFieldName = "最顶级BOM内码";
            this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = true;
            // 报表主键字段名：默认为FIDENTITYID，可以修改
            this.ReportProperty.IdentityFieldName = "FIDENTITYID";
            // 设置精度控制
            //List list = new List();
            //// 数量
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FQty",
            //    DecimalControlFieldName = "FUnitPrecision"
            //});
            //// 单价
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FTAXPRICE",
            //    DecimalControlFieldName = "FPRICEDIGITS"
            //});
            //// 金额
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FALLAMOUNT",
            //    DecimalControlFieldName = "FAMOUNTDIGITS"
            //});
            //this.ReportProperty.DecimalControlFieldList = list;
        }

        public override string GetTableName()
        {
            var result = base.GetTableName();
            return result;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            // 拼接过滤条件 ： filter
            // 略
            // 默认排序字段：需要从filter中取用户设置的排序字段
            string seqFld = string.Format(base.KSQL_SEQ, "最顶级BOM内码");

            DateTime FDate;
            DateTime FEndDate;
            int YEAR = 0;
            string FMaterialid_idsum = "", FSuppiler_idsum = "", FPP_idsum = "";

            Kingdee.BOS.Orm.DataEntity.DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            StringBuilder SQLFilter = new StringBuilder();
            StringBuilder SQLFilter1 = new StringBuilder();
            FDate = Convert.ToDateTime(dyFilter["FDate"]);//期间
            //FEndDate = Convert.ToDateTime(dyFilter["FEndDate"]);//最后发货日期
            //过滤 日期
            if (!FDate.ToString().Contains("0001"))
            {
                //SQLFilter.Append("and t.FDATE>='" + FStartDate + "' and t.FDATE<='" + FEndDate + "'");
                YEAR = FDate.Year;
            }
            DynamicObjectCollection dyCollection = dyFilter["FBOMID"] as DynamicObjectCollection;
            if (dyCollection.Count > 0)
            {
                foreach (var dobj in dyCollection)
                {
                    //string FMaterialid_id = Convert.ToString(dobj["FMATERIALID_ID"]);
                    DynamicObject d = dobj["FBOMID"] as DynamicObject;
                    string FMaterialidNumber = d["ID"].ToString();
                    FMaterialid_idsum = FMaterialid_idsum + "'" + FMaterialidNumber + "'" + ",";

                }
                SQLFilter.Append("and 最顶级BOM内码 IN (" + FMaterialid_idsum.TrimEnd(',') + ")");
            }
            //DynamicObjectCollection dyCollection1 = dyFilter["FSUPPLIERID"] as DynamicObjectCollection;
            //if (dyCollection1.Count > 0)
            //{
            //    foreach (var dobj in dyCollection1)
            //    {
            //        //string FSuppiler_id = Convert.ToString(dobj["FSUPPLIERID_id"]);
            //        DynamicObject d = dobj["FSUPPLIERID"] as DynamicObject;
            //        string FSUPPLIERIDNumber = d["Number"].ToString();
            //        FSuppiler_idsum = FSuppiler_idsum + "'" + FSUPPLIERIDNumber + "'" + ",";
            //    }
            //    SQLFilter.Append("and t25.FSUPPLIERNUMBER IN (" + FSuppiler_idsum.TrimEnd(',') + ")");
            //}
            //DynamicObjectCollection dyCollection2 = dyFilter["FPP"] as DynamicObjectCollection;
            //if (dyCollection2.Count > 0)
            //{
            //    foreach (var dobj in dyCollection2)
            //    {
            //        string FPP_id = Convert.ToString(dobj["FPP_id"]);
            //        FPP_idsum = FPP_idsum + "'" + FPP_id + "'" + ",";
            //    }
            //    SQLFilter.Append("and t6.FENTRYID IN (" + FPP_idsum.TrimEnd(',') + ")");
            //}
            //if (!Convert.ToString(dyFilter["FPL"]).Equals(""))
            //{
            //    SQLFilter.Append("and tz.F_ORA_CATEGORY='" + dyFilter["FPL"] + "'");
            //}
            //if (!Convert.ToString(dyFilter["FGroup_id"]).Equals("0"))
            //{
            //    SQLFilter.Append("and t2.FMATERIALGROUP='" + dyFilter["FGroup_id"] + "'");
            //}
            //if (!Convert.ToString(dyFilter["FBillNo"]).Equals(""))
            //{
            //    SQLFilter1.Append("and t.FBillNo='" + dyFilter["FBillNo"] + "'");
            //}

            // 取数SQL
            string sql = string.Format(@"/*dialect*/
--20210812 研究递归CTE展开BOM;--HYW

/**
--SQL编写目的：
1、根据递归CTE的逻辑，通过一段SQL语句来实现产品物料的BOM多级展开，不需要使用循环或者游标.
2、为什么不建议使用循环或者游标，是因为SQL语言的底层逻辑是集合思想，最高效的处理方案是用集合思维的解决方案，而不是用传统编程的遍历思维。

--编写思路：
1、通过定点子查询查出父项的单层BOM，也就是最顶层产品的BOM，然后根据最顶层产品的BOM进行递归子查询来展开它下面的所有物料的所有BOM（如果有BOM的话）；
2、通过cte2来实现按照子项的BOM版本展开还是按照最新的BOM版本展开；如果产品下面的半成品有子项BOM版本，就根据子项BOM版本展开，如果没有，就根据这个半成品最新的BOM版本来展开；
3、最后查询出结果。

--注意事项：
1、当不输入指定的产品BOM版本作为where条件时，本SQL语句会查询出所有的产品的BOM。
	如果产品下面的子料还有它自己的BOM（即子料是自制件或者委外件），那会继续展开到最底层物料；如果产品下面的子料都是外购件，则不会继续展开，最后的结果里面也会展现出单层BOM。
2、有个“BOM内码和项次组合”字段，这个是用来实现BOM多级展开的层级排序的，其中包含了各级BOM的ID和项次号，格式是“A-B.C-D.E-F”这种，其中‘.’隔开的是不同的层级，‘-’隔开的是BOM内码和项次号（此处项次号是用原项次号+10000的结果）
3、目前语句中设置一个单层BOM的子项明细不能超过9999条分录（包括替代料和返还件的），如果有超过999条分录，则需要把下面select子句中的“10000”改大成10万、20万都可以，根据需求来设置即可。注意：where条件中的项次组合条件也要同步改成(10万+1)、(20万+1)这样，才会展现出0层。
**/

;with cte as
(
	--1、定点（Anchor）子查询，用来查询最顶级的产品的BOM的
	select 
		0 as BOM层次,t1.fid as 最顶级BOM内码
		,t1.FNUMBER as BOM版本,fxwl.FNUMBER as 父项物料代码,fxwl_L.FNAME as 父项物料名称,t3.FSEQ as 分录行号
		,t3.FREPLACEGROUP as 项次,CAST(10000+t3.FREPLACEGROUP AS nvarchar) as 项次组合
		,cast(CAST(t1.fid AS nvarchar)+'-'+CAST(10000+t3.FREPLACEGROUP AS nvarchar) as nvarchar(max)) as BOM内码和项次组合
		,t3.FMATERIALID as 子项物料内码,zxwl.FNUMBER as 子项物料代码,zxwl_L.FNAME as 子项物料名称
		,case when FMATERIALTYPE = 1 then '标准件'
				when FMATERIALTYPE = 2 then '返还件' 
				when FMATERIALTYPE = 3 then '替代件' 
				when FMATERIALTYPE = 4 then '加工费'
				else '未知类型' end as 子项类型
		,t3.FNUMERATOR as 分子,t3.FDENOMINATOR as 分母,t3.FFIXSCRAPQTY as 固定损耗,t3.FSCRAPRATE as 变动损耗,t3.FBOMID,t1.FUSEORGID
		,0 as 是否有子项BOM版本
	from dbo.T_ENG_BOM t1
		join T_BD_MATERIAL fxwl			--用父项关联物料表
			on fxwl.FMATERIALID = t1.FMATERIALID
				and t1.FFORBIDSTATUS = 'A'	--只取未禁用状态的BOM
		join T_BD_MATERIAL_L fxwl_L		--用父项关联物料多语言表
			on fxwl.FMATERIALID = fxwl_l.FMATERIALID and fxwl_L.FLOCALEID =2052
		join T_BD_MATERIALPRODUCE fxwl_P
			on fxwl_P.FMATERIALID = fxwl.FMATERIALID
		join T_ENG_BOMCHILD t3
			on t1.fid = t3.FID		
		join T_BD_MATERIAL zxwl			--用子项关联物料表
			on zxwl.FMATERIALID = t3.FMATERIALID
		join T_BD_MATERIAL_L zxwl_L		--用子项关联物料多语言表
			on zxwl.FMATERIALID = zxwl_L.FMATERIALID and zxwl_L.FLOCALEID =2052
	where 1=1
		and fxwl_P.FISMAINPRD = 1 and t1.FUSEORGID=t1.FCREATEORGID		--物料-生产页签的'可为主产品'属性FISMAINPRD,等于1就意味着可以建立BOM 
		--and t1.FNUMBER in ('1.01.003_V1.0') --这里可以输入一个产品BOM版本,则只会查询一个产品的BOM多级展开;如果这一句注释掉了,就可以查询全部产品物料的多级展开;下面还有一个控制的条件要同步改,一共两个.

	union all

	--2、递归子查询，根据定点子查询的查询结果来关联展开它的所有下级的BOM
	select  
		p.BOM层次+1 as BOM层次,P.最顶级BOM内码 as 最顶级BOM内码
		,t1.FNUMBER as BOM版本,fxwl.FNUMBER as 父项物料代码,fxwl_L.FNAME as 父项物料名称,t3.FSEQ as 分录行号
		,t3.FREPLACEGROUP as 项次,cast(p.项次组合+'.'+CAST(10000+t3.FREPLACEGROUP AS nvarchar) as nvarchar) as 项次组合
		,cast(p.BOM内码和项次组合 +'.'+ ( CAST(t1.FID AS nvarchar) + '-' +CAST(10000+t3.FREPLACEGROUP AS nvarchar) ) as nvarchar(max))  as BOM内码组合
		,t3.FMATERIALID as 子项物料内码,zxwl.FNUMBER as 子项物料代码,zxwl_L.FNAME as 子项物料名称
		,case when FMATERIALTYPE = 1 then '标准件'
				when FMATERIALTYPE = 2 then '返还件' 
				when FMATERIALTYPE = 3 then '替代件' 
				when FMATERIALTYPE = 4 then '加工费'
				else '未知类型' end as 子项类型
		,t3.FNUMERATOR as 分子,t3.FDENOMINATOR as 分母,t3.FFIXSCRAPQTY as 固定损耗,t3.FSCRAPRATE as 变动损耗,t3.FBOMID,t1.FUSEORGID
		,case when p.FBOMID = t1.FID then 1 else 0 end as 是否有子项BOM版本
	from cte P		--调用递归CTE本身
		join dbo.T_ENG_BOM t1
			on t1.FMATERIALID = p.子项物料内码
		join T_BD_MATERIAL fxwl			--父项关联物料表
			on fxwl.FMATERIALID = t1.FMATERIALID
				and t1.FFORBIDSTATUS = 'A'
		join T_BD_MATERIAL_L fxwl_L		--父项关联物料多语言表
			on fxwl.FMATERIALID = fxwl_l.FMATERIALID and fxwl_L.FLOCALEID =2052
		join T_ENG_BOMCHILD t3
			on t1.fid = t3.FID		
		join T_BD_MATERIAL zxwl			--子项关联物料表
			on zxwl.FMATERIALID = t3.FMATERIALID
		join T_BD_MATERIAL_L zxwl_L		--子项关联物料多语言表
			on zxwl.FMATERIALID = zxwl_L.FMATERIALID and zxwl_L.FLOCALEID =2052
			where t1.FUSEORGID=t1.FCREATEORGID
)
--select * from cte		----调试第一段CTE
,cte2_ZuiXinZiXiangBom as		--这个cte2是用来取非0层的子项BOM的最新BOM版本的,然后和0层的父项信息union在一起
(
	select 
		t1.BOM层次 as BOM层级,t1.最顶级BOM内码,t1.BOM版本
		,t1.父项物料代码 as 物料代码,t1.父项物料名称 as 物料名称,0 as 分录行号,0 as 项次,t1.项次组合 as 项次组合,BOM内码和项次组合,0 as 子项物料内码,'' as 子项物料代码,'' as 子项物料名称,'最顶层父项' as 子项类型,0 as 分子,0 as 分母,0 as 固定损耗,0 as 变动损耗,0 as BOM内码,t1.FUSEORGID,t1.是否有子项BOM版本
		,dense_rank() over(partition by t1.最顶级BOM内码,t1.父项物料代码 order by t1.BOM版本 desc) as BOM版本号分区
	from cte t1
	where 1=1 
		and t1.BOM层次 = 0 and t1.项次组合 = '10001'		--这里是只显示0层的产品
		--and t1.BOM版本 in ('1.01.003_V1.0')	--这里可以输入一个产品BOM版本,则只会查询一个产品的BOM多级展开;如果这一句注释掉了,就可以查询全部产品物料的多级展开;上面还有一个控制的条件要同步改,一共两个.

	union 
	select 
		t1.BOM层次+1 as BOM层级,t1.最顶级BOM内码,t1.BOM版本
		,t1.子项物料代码 as 物料代码,t1.子项物料名称 as 物料名称,t1.分录行号 as 分录行号,t1.项次 as 项次,t1.项次组合 as 项次组合,BOM内码和项次组合,0 as 子项物料内码,t1.子项物料代码 as 子项物料代码,'' as 子项物料名称,t1.子项类型 as 子项类型,t1.分子 as 分子,t1.分母 as 分母,t1.固定损耗 as 固定损耗,t1.变动损耗 as 变动损耗,t1.FBOMID as BOM内码,t1.FUSEORGID,t1.是否有子项BOM版本
		,dense_rank() over(partition by t1.最顶级BOM内码,t1.父项物料代码 order by t1.BOM层次+1,t1.是否有子项BOM版本 desc,t1.BOM版本 desc) as BOM版本号分区	--通过这个字段标识最新版本的BOM，按照父项物料分区之后，把BOM版本降序排列，BOM版本高的排序序号就是1
	from cte t1
	where 1=1
		--and t1.BOM层次+1 <=2	--可以通过BOM层次字段来控制递归循环的次数,如果这里不加控制，那系统默认最多是循环100次
)
--select * from cte2_ZuiXinZiXiangBom t2		----调试第二段CTE
select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) 序号, t2.BOM层级 as BOM层级
		,t2.物料代码 as 子项物料代码,t2.物料名称 as 物料名称,t2.分录行号 as 分录行号,t2.项次 as 项次,t2.子项类型 as 子项类型,t2.分子 as 分子,t2.分母 as 分母
		,t2.固定损耗 as 固定损耗,t2.变动损耗 as 变动损耗
		,t2.FUSEORGID,t2.项次组合 as 项次组合,t2.BOM内码和项次组合,t2.BOM内码 as 子项BOM版本内码,t2.BOM版本 as 所属BOM,t2.最顶级BOM内码,CONVERT(VARCHAR, '') 父项物料编码	--这一行的可以注释掉,只是为了排查SQL问题用的.
		INTO #TempTable
from  cte2_ZuiXinZiXiangBom t2
where 1=1
	and t2.BOM版本号分区 = 1		--通过“BOM版本号分区”标识最新版本的BOM，按照父项物料分区之后，把BOM版本降序排列，BOM版本高的值就是1
	and ( (t2.BOM层级 = 0 and t2.项次组合 = '10001' ) or (t2.BOM层级 > 0) )	--这个是为了查询出最终的结果.
	--and t2.FUSEORGID =100003	--蓝海机械账套的‘总装事业部’组织
order by t2.BOM内码和项次组合

select DISTINCT t2.FNUMBER,t3.FNAME,t5.FDATAVALUE,t7.FNAME FDW,T8.FNAME FFL,T3.FSPECIFICATION,t2.F_ORA_GOODSTATUS,T9.FBOXSTANDARDQTY INTO #MATERIAL from  T_BD_MATERIAL T2 
LEFT JOIN T_BD_MATERIAL_L T3 ON T2.FMATERIALID=T3.FMATERIALID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T4 ON T2.F_ORA_PP=t4.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L T5 ON T4.FENTRYID=T5.FENTRYID
LEFT JOIN t_BD_MaterialBase T6 ON T2.FMATERIALID=t6.FMATERIALID
LEFT JOIN T_BD_UNIT_L T7 ON T6.FBASEUNITID=T7.FUNITID
LEFT JOIN T_BD_MATERIALGROUP_L T8 ON T2.FMATERIALGROUP=T8.FID
LEFT JOIN t_BD_MaterialStock T9 ON T2.FMATERIALID=T9.FMATERIALID
WHERE T2.FDOCUMENTSTATUS='C' AND T3.FLOCALEID=2052 AND T5.FLOCALEID=2052
AND T7.FLOCALEID=2052 and t2.FCREATEORGID=t2.FUSEORGID


-------------------------1月份价格------------------------------
select * INTO #January from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
where
dateadd(dd,-day('{3}-01-01')+1,'{3}-01-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{3}-01-01'),dateadd(m,1,'{3}-01-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{3}-01-01')+1,'{3}-01-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-01-01'),dateadd(m,1,'{3}-01-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{3}-01-01')+1,'{3}-01-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{3}-01-01'),dateadd(m,1,'{3}-01-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-01-01')+1,'{3}-01-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{3}-01-01')+1,'{3}-01-10'))
   )t
   where t.row_num=1

-------------------------2月份价格------------------------------
select * INTO #February from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
where
dateadd(dd,-day('{3}-02-01')+1,'{3}-02-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{3}-02-01'),dateadd(m,1,'{3}-02-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{3}-02-01')+1,'{3}-02-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-02-01'),dateadd(m,1,'{3}-02-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{3}-02-01')+1,'{3}-02-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{3}-02-01'),dateadd(m,1,'{3}-02-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-02-01')+1,'{3}-02-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{3}-02-01')+1,'{3}-02-10'))
   )t
   where t.row_num=1

   
-------------------------3月份价格------------------------------
select * INTO #March from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
where
dateadd(dd,-day('{3}-03-01')+1,'{3}-03-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{3}-03-01'),dateadd(m,1,'{3}-03-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{3}-03-01')+1,'{3}-03-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-03-01'),dateadd(m,1,'{3}-03-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{3}-03-01')+1,'{3}-03-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{3}-03-01'),dateadd(m,1,'{3}-03-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-03-01')+1,'{3}-03-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{3}-03-01')+1,'{3}-03-10'))
   )t
   where t.row_num=1

-------------------------4月份价格------------------------------
select * INTO #April from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
where
dateadd(dd,-day('{3}-04-01')+1,'{3}-04-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{3}-04-01'),dateadd(m,1,'{3}-04-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{3}-04-01')+1,'{3}-04-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-04-01'),dateadd(m,1,'{3}-04-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{3}-04-01')+1,'{3}-04-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{3}-04-01'),dateadd(m,1,'{3}-04-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-04-01')+1,'{3}-04-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{3}-04-01')+1,'{3}-04-10'))
   )t
   where t.row_num=1

-------------------------5月份价格------------------------------
select * INTO #May from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
where
dateadd(dd,-day('{3}-05-01')+1,'{3}-05-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{3}-05-01'),dateadd(m,1,'{3}-05-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{3}-05-01')+1,'{3}-05-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-05-01'),dateadd(m,1,'{3}-05-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{3}-05-01')+1,'{3}-05-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{3}-05-01'),dateadd(m,1,'{3}-05-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-05-01')+1,'{3}-05-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{3}-05-01')+1,'{3}-05-10'))
   )t
   where t.row_num=1

-------------------------6月份价格------------------------------
select * INTO #June from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
where
dateadd(dd,-day('{3}-06-01')+1,'{3}-06-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{3}-06-01'),dateadd(m,1,'{3}-06-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{3}-06-01')+1,'{3}-06-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-06-01'),dateadd(m,1,'{3}-06-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{3}-06-01')+1,'{3}-06-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{3}-06-01'),dateadd(m,1,'{3}-06-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-06-01')+1,'{3}-06-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{3}-06-01')+1,'{3}-06-10'))
   )t
   where t.row_num=1

-------------------------7月份价格------------------------------
select * INTO #July from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
where
dateadd(dd,-day('{3}-07-01')+1,'{3}-07-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{3}-07-01'),dateadd(m,1,'{3}-07-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{3}-07-01')+1,'{3}-07-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-07-01'),dateadd(m,1,'{3}-07-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{3}-07-01')+1,'{3}-07-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{3}-07-01'),dateadd(m,1,'{3}-07-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-07-01')+1,'{3}-07-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{3}-07-01')+1,'{3}-07-10'))
   )t
   where t.row_num=1

-------------------------8月份价格------------------------------
select * INTO #August from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
where
dateadd(dd,-day('{3}-08-01')+1,'{3}-08-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{3}-08-01'),dateadd(m,1,'{3}-08-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{3}-08-01')+1,'{3}-08-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-08-01'),dateadd(m,1,'{3}-08-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{3}-08-01')+1,'{3}-08-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{3}-08-01'),dateadd(m,1,'{3}-08-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-08-01')+1,'{3}-08-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{3}-08-01')+1,'{3}-08-10'))
   )t
   where t.row_num=1

-------------------------9月份价格------------------------------
select * INTO #September from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
where
dateadd(dd,-day('{3}-09-01')+1,'{3}-09-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{3}-09-01'),dateadd(m,1,'{3}-09-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{3}-09-01')+1,'{3}-09-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-09-01'),dateadd(m,1,'{3}-09-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{3}-09-01')+1,'{3}-09-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{3}-09-01'),dateadd(m,1,'{3}-09-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-09-01')+1,'{3}-09-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{3}-09-01')+1,'{3}-09-10'))
   )t
   where t.row_num=1

-------------------------10月份价格------------------------------
select * INTO #October from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
where
dateadd(dd,-day('{3}-10-01')+1,'{3}-10-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{3}-10-01'),dateadd(m,1,'{3}-10-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{3}-10-01')+1,'{3}-10-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-10-01'),dateadd(m,1,'{3}-10-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{3}-10-01')+1,'{3}-10-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{3}-10-01'),dateadd(m,1,'{3}-10-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-10-01')+1,'{3}-10-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{3}-10-01')+1,'{3}-10-10'))
   )t
   where t.row_num=1

-------------------------11月份价格------------------------------
select * INTO #November from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
where
dateadd(dd,-day('{3}-11-01')+1,'{3}-11-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{3}-11-01'),dateadd(m,1,'{3}-11-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{3}-11-01')+1,'{3}-11-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-11-01'),dateadd(m,1,'{3}-11-11'))) and T.FEXPIRYDATE='2100-01-01')
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{3}-11-01')+1,'{3}-11-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{3}-11-01'),dateadd(m,1,'{3}-11-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-11-01')+1,'{3}-11-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{3}-11-01')+1,'{3}-11-10'))
   )t
   where t.row_num=1

-------------------------12月份价格------------------------------
select * INTO #December from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
where
dateadd(dd,-day('{3}-12-01')+1,'{3}-12-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{3}-12-01'),dateadd(m,1,'{3}-12-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{3}-12-01')+1,'{3}-12-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-12-01'),dateadd(m,1,'{3}-12-11'))) and T.FEXPIRYDATE='2100-01-01')
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{3}-12-01')+1,'{3}-12-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{3}-12-01'),dateadd(m,1,'{3}-12-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-12-01')+1,'{3}-12-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{3}-12-01')+1,'{3}-12-10'))
   )t
   where t.row_num=1

select * into #suppiler1  from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{3}-01-01')+1,'{3}-01-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{3}-12-01'),dateadd(m,1,'{3}-12-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{3}-01-01')+1,'{3}-01-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-12-01'),dateadd(m,1,'{3}-12-11'))) and T.FEXPIRYDATE='2100-01-01')
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{3}-01-01')+1,'{3}-01-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{3}-12-01'),dateadd(m,1,'{3}-12-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{3}-01-01')+1,'{3}-01-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{3}-01-01')+1,'{3}-01-10'))
   )t
   where t.row_num=1

SELECT s1.fname,S1.FNUMBER INTO #suppiler
FROM #suppiler1 s1
JOIN (
    SELECT fnumber, MAX(FTAXPRICE) AS max_price
    FROM #suppiler1
    GROUP BY fnumber
) s2 ON s1.fnumber = s2.fnumber AND s1.FTAXPRICE = s2.max_price

select t.分录行号,最顶级BOM内码,子项物料代码 货品编码,物料名称 品名,子项类型,项次,BOM层级,
case when 子项类型<>'最顶层父项' and 子项类型<>'加工费' and 固定损耗<>0 and 分母<>0 then (分子/分母)*(1+固定损耗/100)
when 子项类型<>'最顶层父项' and 子项类型<>'加工费' and 变动损耗<>0 and 分母<>0 then (分子/分母)*(1+变动损耗/100)
else 分子 end 用量,
t1.FDATAVALUE 品牌,
t1.FSPECIFICATION 规格,
t1.FFL 分类,
t1.FDW 单位,
t.父项物料编码,
T1.FBOXSTANDARDQTY,
t14.fname fsuppiler,
isnull(t2.FTAXPRICE,0) [1月],
isnull(t3.FTAXPRICE,0) [2月],
isnull(t4.FTAXPRICE,0) [3月],
isnull(t5.FTAXPRICE,0) [4月],
isnull(t6.FTAXPRICE,0) [5月],
isnull(t7.FTAXPRICE,0) [6月],
isnull(t8.FTAXPRICE,0) [7月],
isnull(t9.FTAXPRICE,0) [8月],
isnull(t10.FTAXPRICE,0) [9月],
isnull(t11.FTAXPRICE,0) [10月],
isnull(t12.FTAXPRICE,0) [11月],
isnull(t13.FTAXPRICE,0) [12月]
into #temptable1
from #TempTable t
left join #MATERIAL t1 on t.子项物料代码=t1.FNUMBER 
left join #January t2 on t.子项物料代码=t2.FNUMBER and 子项类型<>'最顶层父项'
left join #February t3 on t.子项物料代码=t3.FNUMBER and 子项类型<>'最顶层父项'
left join #March t4 on t.子项物料代码=t4.FNUMBER and 子项类型<>'最顶层父项'
left join #April t5 on t.子项物料代码=t5.FNUMBER and 子项类型<>'最顶层父项'
left join #May t6 on t.子项物料代码=t6.FNUMBER and 子项类型<>'最顶层父项'
left join #June t7 on t.子项物料代码=t7.FNUMBER and 子项类型<>'最顶层父项'
left join #July t8 on t.子项物料代码=t8.FNUMBER and 子项类型<>'最顶层父项'
left join #August t9 on t.子项物料代码=t9.FNUMBER and 子项类型<>'最顶层父项'
left join #September t10 on t.子项物料代码=t10.FNUMBER and 子项类型<>'最顶层父项'
left join #October t11 on t.子项物料代码=t11.FNUMBER and 子项类型<>'最顶层父项'
left join #November t12 on t.子项物料代码=t12.FNUMBER and 子项类型<>'最顶层父项'
left join #December t13 on t.子项物料代码=t13.FNUMBER and 子项类型<>'最顶层父项'
left join #suppiler t14 on t.子项物料代码=t14.fnumber and 子项类型<>'最顶层父项'
order by t.序号


update t set t.父项物料编码=t1.货品编码 from #temptable1 t 
left join #temptable1 t1 on t.最顶级BOM内码=t1.最顶级BOM内码
where t1.子项类型='最顶层父项' and t1.货品编码 is not null

update t set t.[1月]=t1.FTAXPRICE from  #temptable1 t
left join #January t1 on t.父项物料编码=t1.FNUMBER and 子项类型='加工费' and t1.FPRICETYPE='3'
where t1.FTAXPRICE is not null 

update t set t.[2月]=t1.FTAXPRICE from  #temptable1 t
left join #February t1 on t.父项物料编码=t1.FNUMBER and 子项类型='加工费' and t1.FPRICETYPE='3'
where t1.FTAXPRICE is not null 

update t set t.[3月]=t1.FTAXPRICE from  #temptable1 t
left join #March t1 on t.父项物料编码=t1.FNUMBER and 子项类型='加工费' and t1.FPRICETYPE='3'
where t1.FTAXPRICE is not null 

update t set t.[4月]=t1.FTAXPRICE from  #temptable1 t
left join #April t1 on t.父项物料编码=t1.FNUMBER and 子项类型='加工费' and t1.FPRICETYPE='3'
where t1.FTAXPRICE is not null 

update t set t.[5月]=t1.FTAXPRICE from  #temptable1 t
left join #May t1 on t.父项物料编码=t1.FNUMBER and 子项类型='加工费' and t1.FPRICETYPE='3'
where t1.FTAXPRICE is not null 

update t set t.[6月]=t1.FTAXPRICE from  #temptable1 t
left join #June t1 on t.父项物料编码=t1.FNUMBER and 子项类型='加工费' and t1.FPRICETYPE='3'
where t1.FTAXPRICE is not null 

update t set t.[7月]=t1.FTAXPRICE from  #temptable1 t
left join #July t1 on t.父项物料编码=t1.FNUMBER and 子项类型='加工费' and t1.FPRICETYPE='3'
where t1.FTAXPRICE is not null 

update t set t.[8月]=t1.FTAXPRICE from  #temptable1 t
left join #August t1 on t.父项物料编码=t1.FNUMBER and 子项类型='加工费' and t1.FPRICETYPE='3'
where t1.FTAXPRICE is not null 

update t set t.[9月]=t1.FTAXPRICE from  #temptable1 t
left join #September t1 on t.父项物料编码=t1.FNUMBER and 子项类型='加工费' and t1.FPRICETYPE='3'
where t1.FTAXPRICE is not null 

update t set t.[10月]=t1.FTAXPRICE from  #temptable1 t
left join #October t1 on t.父项物料编码=t1.FNUMBER and 子项类型='加工费' and t1.FPRICETYPE='3'
where t1.FTAXPRICE is not null 

update t set t.[11月]=t1.FTAXPRICE from  #temptable1 t
left join #November t1 on t.父项物料编码=t1.FNUMBER and 子项类型='加工费' and t1.FPRICETYPE='3'
where t1.FTAXPRICE is not null 

update t set t.[12月]=t1.FTAXPRICE from  #temptable1 t
left join #December t1 on t.父项物料编码=t1.FNUMBER and 子项类型='加工费' and t1.FPRICETYPE='3'
where t1.FTAXPRICE is not null 

UPDATE T SET 
T.[1月]=t.[1月]*t.用量,
T.[2月]=t.[2月]*t.用量,
T.[3月]=t.[3月]*t.用量,
T.[4月]=t.[4月]*t.用量,
T.[5月]=t.[5月]*t.用量,
T.[6月]=t.[6月]*t.用量,
T.[7月]=t.[7月]*t.用量,
T.[8月]=t.[8月]*t.用量,
T.[9月]=t.[9月]*t.用量,
T.[10月]=t.[10月]*t.用量,
T.[11月]=t.[11月]*t.用量,
T.[12月]=t.[12月]*t.用量
from #temptable1 t
where t.子项类型='标准件'

select 最顶级BOM内码,sum([1月])[1月],sum([2月])[2月],sum([3月])[3月],sum([4月])[4月],sum([5月])[5月],sum([6月])[6月],
sum([7月])[7月],sum([8月])[8月],sum([9月])[9月],sum([10月])[10月],sum([11月])[11月],sum([12月])[12月]
into #SUMAMOUNT from #temptable1
group by 最顶级BOM内码


UPDATE T SET 
T.[1月]=t1.[1月],
T.[2月]=t1.[2月],
T.[3月]=t1.[3月],
T.[4月]=t1.[4月],
T.[5月]=t1.[5月],
T.[6月]=t1.[6月],
T.[7月]=t1.[7月],
T.[8月]=t1.[8月],
T.[9月]=t1.[9月],
T.[10月]=t1.[10月],
T.[11月]=t1.[11月],
T.[12月]=t1.[12月]
 from #temptable1 t
left join #SUMAMOUNT t1 on t.最顶级BOM内码=t1.最顶级BOM内码
where t.子项类型='最顶层父项'

SELECT *,{0} INTO {1} FROM (
select 分录行号 FLHH,最顶级BOM内码,货品编码 FHPBM,品名 FPM,子项类型 FZXLX,用量 FYL,品牌 FPP,规格 FGG,分类 FFL,单位 FDW,FBOXSTANDARDQTY,fsuppiler,
[1月] F1Y,[2月] F2Y,[3月] F3Y,[4月] F4Y,[5月] F5Y,[6月] F6Y,[7月] F7Y,[8月] F8Y,[9月] F9Y
,[10月] F10Y,[11月] F11Y,[12月] F12Y
from #temptable1 
UNION ALL
select 0 FLHH,0 最顶级BOM内码,t.FNUMBER FHPBM,T2.FNAME FPM,'最顶层父项' FZXLX,0 FYL,T5.FDATAVALUE FPP,
T2.FSPECIFICATION FGG,T8.FNAME FFL,t7.FNAME FDW,T9.FBOXSTANDARDQTY,'' fsuppiler,0 F1Y,0 F2Y,0 F3Y,0 F4Y,0 F5Y,0 F6Y,0 F7Y,0 F8Y
,0 F9Y,0 F10Y,0 F11Y,0 F12Y
from T_BD_MATERIAL T
LEFT JOIN t_BD_MaterialProduce T1 ON T.FMATERIALID=T1.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L T2 ON T.FMATERIALID=T2.FMATERIALID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T4 ON T.F_ORA_PP=t4.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L T5 ON T4.FENTRYID=T5.FENTRYID
LEFT JOIN t_BD_MaterialBase T6 ON T.FMATERIALID=t6.FMATERIALID
LEFT JOIN T_BD_UNIT_L T7 ON T6.FBASEUNITID=T7.FUNITID
LEFT JOIN T_BD_MATERIALGROUP_L T8 ON T.FMATERIALGROUP=T8.FID
LEFT JOIN t_BD_MaterialStock T9 ON T.FMATERIALID=T9.FMATERIALID
where T.FCreateOrgId=T.FUseOrgId  AND T1.FISMAINPRD=1
AND T.FNUMBER NOT IN (select 货品编码 from #temptable1 where 子项类型='最顶层父项')
AND T.FDOCUMENTSTATUS='C' AND T2.FLOCALEID=2052 AND T5.FLOCALEID=2052
AND T7.FLOCALEID=2052 
)T WHERE 最顶级BOM内码 IS NOT NULL {2}
                                        ", seqFld, tableName, SQLFilter, YEAR);
            DBUtils.ExecuteDynamicObject(this.Context, sql);
        }

        protected override string GetIdentityFieldIndexSQL(string tableName)
        {
            string result = base.GetIdentityFieldIndexSQL(tableName);
            return result;
        }

        protected override void ExecuteBatch(List<string> listSql)
        {
            base.ExecuteBatch(listSql);
        }

        protected override string GetSummaryColumsSQL(List<SummaryField> summaryFields)
        {
            var result = base.GetSummaryColumsSQL(summaryFields);
            return result;
        }

        protected override System.Data.DataTable GetListData(string sSQL)
        {
            var result = base.GetListData(sSQL);
            return result;
        }

        protected override System.Data.DataTable GetReportData(IRptParams filter)
        {
            var result = base.GetReportData(filter);
            return result;
        }

        protected override System.Data.DataTable GetReportData(string tablename, IRptParams filter)
        {
            var result = base.GetReportData(tablename, filter);
            return result;
        }

        public override int GetRowsCount(IRptParams filter)
        {
            var result = base.GetRowsCount(filter);
            return result;
        }

        protected override string BuilderFromWhereSQL(IRptParams filter)
        {
            string result = base.BuilderFromWhereSQL(filter);
            return result;
        }

        protected override string BuilderSelectFieldSQL(IRptParams filter)
        {
            string result = base.BuilderSelectFieldSQL(filter);
            return result;
        }

        protected override string BuilderTempTableOrderBySQL(IRptParams filter)
        {
            string result = base.BuilderTempTableOrderBySQL(filter);
            return result;
        }

        public override void CloseReport()
        {
            base.CloseReport();
        }

        protected override string CreateGroupSummaryData(IRptParams filter, string tablename)
        {
            string result = base.CreateGroupSummaryData(filter, tablename);
            return result;
        }

        protected override void CreateTempTable(string sSQL)
        {
            base.CreateTempTable(sSQL);
        }

        public override void DropTempTable()
        {
            base.DropTempTable();
        }

        public override System.Data.DataTable GetList(IRptParams filter)
        {
            var result = base.GetList(filter);
            return result;
        }

        public override List<long> GetOrgIdList(IRptParams filter)
        {
            var result = base.GetOrgIdList(filter);
            return result;
        }

        public override List<Kingdee.BOS.Core.Metadata.TreeNode> GetTreeNodes(IRptParams filter)
        {
            var result = base.GetTreeNodes(filter);
            return result;
        }
    }
}
